/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Manoel Afonso Filho
 */
public class BancoDados {

    public static Connection getConnection(){
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your PostgreSQL JDBC Driver? "
                    + "Include in your library path!");
            e.printStackTrace();
            return null;
        }

        System.out.println("PostgreSQL JDBC Driver Registered!");

        Connection connection = null;
        try {
            connection = DriverManager.getConnection(
                    "jdbc:postgresql://127.0.0.1:5432/engsoft", "postgres",
                    "kaminari!");
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return null;
        }

        if (connection != null) {
            System.out.println("You made it, take control your database now!");
        } else {
            System.out.println("Failed to make connection!");
        }
        
        return connection;
    }
    
    /*
     * INSERT INTO ocorrencia(
            id, hora_inicio, hora_final, tipo, descricao, usuario, camera)
    VALUES (?, ?, ?, ?, ?, ?, ?);
     */
    
    public static int cadastrarOcorrencia(String horaInicio, String horaFim, int tipo, String desc) throws SQLException{
        String query = "INSERT INTO ocorrencia(hora_inicio, hora_final, tipo, descricao, usuario, camera) VALUES (?, ?, ?, ?, ?, ?)";
        Connection con = getConnection();
        con.setAutoCommit(false);
        PreparedStatement ps = con.prepareStatement(query);
        ps.setString(1, horaInicio);
        ps.setString(2, horaFim);
        ps.setInt(3, tipo);
        ps.setString(4, desc);
        ps.setInt(5, 1); //usuário 1
        ps.setInt(6, 1); //camera 1
        ps.executeUpdate();
        con.commit();
        
        int key = 0;
        Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = statement.executeQuery("select * from ocorrencia");
        resultSet.afterLast();
        boolean last = resultSet.last();
        if(last){
            key = resultSet.getInt("id");
        }
        
        ps.close();
        con.setAutoCommit(true);
        con.close();
        return key;
    }
}
